﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management;


namespace DBCodeGenerator
{
    public class CsharpFunctionStub
    {
        public class StubParam
        {
            public string name { get; set; }
            public DataType type { get; set; }
        }

        public string ReturnType { get; set; }
        public string FileName { get; set; }
        public List<StubParam> Parameters { get; set; }
        public string FunctionName { get; set; }
        public string stdProcname { get; set; }
        public string FunctionType { get; set; }
        public string fileNamespace { get; set; }
        public string classnamespace { get; set; }
        public string classname { get; set; }


        private string tempfilename = "funcstub.temp";
        private string connectionbasefilename = "SQLServerConnection.cs";

        private const string RETURN_TYPE = "[RETURN_TYPE]";
        private const string FUNCTION_NAME = "[FUNCTION_NAME]";
        private const string FUNCTION_PARAMS = "[FUNCTION_PARAMS]";
        private const string FUNCTION_BODY = "[FUNCTION_BODY]";
        private const string EXCEPTION_BODY = "[EXCEPTION_BODY]";
        private const string STOREDPROC_NAME = "[STOREDPROC_NAME]";
        private const string CLASS_NAME = "[CLASS_NAME]";
        private const string NAMESPACE = "[NAMESPACE]";
        private const string CLASS_BODY = "[CLASS_BODY]";

        public void GenerateFunction()
        {
            if (ReturnType == CSharpGenerator.RETURNTYPE_DS)
            {
                this.WriteQueryFunction();
            }
            else if (ReturnType == CSharpGenerator.RETURNTYPE_BOOL)
            {
                this.WriteNonQueryFunction();
            }
            else if(ReturnType == CSharpGenerator.RETURNTYPE_SINGLEVALUE)
            {
                this.WriteScalarFunction();
            }
        }

        private void WriteQueryFunction()
        {
            string functionstub = "";
            string paramstring = "";
            StringBuilder funcbody = new StringBuilder();
            try
            {
                functionstub = this.GetQueryTemplate();
                functionstub = functionstub.Replace(FUNCTION_NAME, FunctionName);
                FileStream fs = File.Open(tempfilename, FileMode.Append);
                StreamWriter sw = new StreamWriter(fs);

                foreach (StubParam param in Parameters)
                {
                    string fullprm = GetCSharptype(param.type) + " " + param.name;
                    paramstring = fullprm + "," + paramstring;
                }

                paramstring = paramstring.TrimEnd(new char[] { ',' });
                functionstub = functionstub.Replace(FUNCTION_PARAMS, paramstring);
                functionstub = functionstub.Replace(STOREDPROC_NAME, stdProcname);
                funcbody.AppendLine("");

                foreach (StubParam param in Parameters)
                {
                    funcbody.AppendLine("\t\t\tSqlParameter sqlp_" + param.name + " = new SqlParameter(\"@" + param.name + "\", SqlDbType." + param.type.SqlDataType.ToString() + ");");
                    funcbody.AppendLine("\t\t\tsqlp_" + param.name + ".Value ="+param.name+";");
                    funcbody.AppendLine("\t\t\tsqlparams.Add(sqlp_" + param.name + ");");
                }

                funcbody.AppendLine("\t\t\treturn connection.GetDataset(\"" + stdProcname + "\", sqlparams);");
                functionstub = functionstub.Replace(FUNCTION_BODY, funcbody.ToString());

                sw.Write(functionstub);
                sw.Close();
                fs.Close();
            }
            catch (Exception ex)
            {
               
            }
        }

        private void WriteNonQueryFunction()
        {

            string functionstub = "";
            string paramstring = "";
            StringBuilder funcbody = new StringBuilder();
            try
            {
                functionstub = this.GetNonQueryTemplate();
                functionstub = functionstub.Replace(FUNCTION_NAME, FunctionName);
                FileStream fs = File.Open(tempfilename, FileMode.Append);
                StreamWriter sw = new StreamWriter(fs);

                foreach (StubParam param in Parameters)
                {
                    string fullprm = GetCSharptype(param.type) + " " + param.name;
                    paramstring = fullprm + "," + paramstring;
                }

                paramstring = paramstring.TrimEnd(new char[] { ',' });
                functionstub = functionstub.Replace(FUNCTION_PARAMS, paramstring);
                functionstub = functionstub.Replace(STOREDPROC_NAME, stdProcname);
                funcbody.AppendLine("");

                foreach (StubParam param in Parameters)
                {
                    funcbody.AppendLine("\t\tSqlParameter sqlp_" + param.name + " = new SqlParameter(\"@" + param.name + "\", SqlDbType." + param.type.SqlDataType.ToString() + ");");
                    funcbody.AppendLine("\t\tsqlp_" + param.name + ".Value =" + param.name + ";");
                    funcbody.AppendLine("\t\tsqlparams.Add(sqlp_" + param.name + ");");
                }

                functionstub = functionstub.Replace(FUNCTION_BODY, funcbody.ToString());
                sw.Write(functionstub);
                sw.Close();
                fs.Close();
            }
            catch (Exception ex)
            {

            }
        }

        private void WriteScalarFunction()
        {
            string functionstub = "";
            string paramstring = "";
            StringBuilder funcbody = new StringBuilder();
            try
            {
                functionstub = this.GetScalarTemplate();
                functionstub = functionstub.Replace(FUNCTION_NAME, FunctionName);
                FileStream fs = File.Open(tempfilename, FileMode.Append);
                StreamWriter sw = new StreamWriter(fs);

                foreach (StubParam param in Parameters)
                {
                    string fullprm = GetCSharptype(param.type) + " " + param.name;
                    paramstring = fullprm + "," + paramstring;
                }

                paramstring = paramstring.TrimEnd(new char[] { ',' });
                functionstub = functionstub.Replace(FUNCTION_PARAMS, paramstring);
                functionstub = functionstub.Replace(STOREDPROC_NAME, stdProcname);
                funcbody.AppendLine("");

                foreach (StubParam param in Parameters)
                {
                    funcbody.AppendLine("\t\t\tSqlParameter sqlp_" + param.name + " = new SqlParameter(\"@" + param.name + "\", SqlDbType." + param.type.SqlDataType.ToString() + ");");
                    funcbody.AppendLine("\t\t\tsqlp_" + param.name + ".Value =" + param.name + ";");
                    funcbody.AppendLine("\t\t\tsqlparams.Add(sqlp_" + param.name + ");");
                }

                funcbody.AppendLine("\t\t\treturn connection.GetScalarValue(\"" + stdProcname + "\", sqlparams);");
                functionstub = functionstub.Replace(FUNCTION_BODY, funcbody.ToString());

                sw.Write(functionstub);
                sw.Close();
                fs.Close();
            }
            catch (Exception ex)
            {

            }
        }

        private string GetCSharptype(DataType type)
        {
            string Cshtype = "";

            switch (type.SqlDataType)
            {
                case SqlDataType.BigInt:
                    Cshtype = "int";
                    break;
                case SqlDataType.Binary:
                    Cshtype = "byte[]";
                    break;
                case SqlDataType.Bit:
                    Cshtype = "bool";
                    break;
                case SqlDataType.Char:
                    Cshtype = "char";
                    break;
                case SqlDataType.Date:
                    Cshtype = "DateTime";
                    break;
                case SqlDataType.DateTime:
                    Cshtype = "DateTime";
                    break;
                case SqlDataType.DateTime2:
                    Cshtype = "DateTime";
                    break;
                case SqlDataType.DateTimeOffset:
                    Cshtype = "DateTime";
                    break;
                case SqlDataType.Decimal:
                    Cshtype = "decimal";
                    break;
                case SqlDataType.Float:
                    Cshtype = "float";
                    break;
                case SqlDataType.Geography:
                    Cshtype = "unknown"; 
                    break;
                case SqlDataType.Geometry:
                    Cshtype = "unknown";
                    break;
                case SqlDataType.HierarchyId:
                    Cshtype = "unknown";
                    break;
                    
                case SqlDataType.Image:
                    Cshtype = "byte[]";
                    break;
                case SqlDataType.Int:
                    Cshtype = "Int32";
                    break;
                case SqlDataType.Money:
                    Cshtype = "decimal";
                    break;
                case SqlDataType.NChar:
                    Cshtype = "string";
                    break;
                case SqlDataType.NText:
                    Cshtype = "string";
                    break;
                case SqlDataType.NVarChar:
                    Cshtype = "string";
                    break;
                case SqlDataType.NVarCharMax:
                    Cshtype = "string";
                    break;
                case SqlDataType.None:
                    break;
                case SqlDataType.Numeric:
                    Cshtype = "uint";
                    break;
                case SqlDataType.Real:
                    Cshtype = "float";
                    break;
                case SqlDataType.SmallDateTime:
                    Cshtype = "DateTime";
                    break;
                case SqlDataType.SmallInt:
                    Cshtype = "Int16";
                    break;
                case SqlDataType.SmallMoney:
                    Cshtype = "decimal";
                    break;
                case SqlDataType.SysName:
                    Cshtype = "string";
                    break;
                case SqlDataType.Text:
                    Cshtype = "string";
                    break;
                case SqlDataType.Time:
                    Cshtype = "DateTime";
                    break;
                case SqlDataType.Timestamp:
                    Cshtype = "DateTime";
                    break;
                case SqlDataType.TinyInt:
                    Cshtype = "int";
                    break;
                case SqlDataType.UniqueIdentifier:
                    Cshtype = "Guid";
                    break;
                case SqlDataType.UserDefinedDataType:
                    Cshtype = "userdefined";
                    break;
                case SqlDataType.UserDefinedTableType:
                    Cshtype = "userdefined";
                    break;
                case SqlDataType.UserDefinedType:
                    Cshtype = "userdefined";
                    break;
                case SqlDataType.VarBinary:
                    Cshtype = "byte[]";
                    break;
                case SqlDataType.VarBinaryMax:
                    Cshtype = "byte[]";
                    break;
                case SqlDataType.VarChar:
                    Cshtype = "string";
                    break;
                case SqlDataType.VarCharMax:
                    Cshtype = "string";
                    break;
                case SqlDataType.Variant:
                    Cshtype = "object";
                    break;
                case SqlDataType.Xml:
                    Cshtype = "string";
                    break;

                default:
                    break;
            }

            return Cshtype;
        }

        private string GetQueryTemplate()
        {
            string templatestring = "\n\n";
            try
            {
                FileStream fs = File.OpenRead("templates\\csharptquery.template");
                StreamReader sr = new StreamReader(fs);
                templatestring = templatestring+ sr.ReadToEnd();
                
                sr.Close();
                fs.Close();
            }
            catch (Exception ex)
            {

            }

            return templatestring;
        }

        private string GetScalarTemplate()
        {
            string templatestring = "\n\n";
            try
            {
                FileStream fs = File.OpenRead("templates\\csharptscalar.template");
                StreamReader sr = new StreamReader(fs);
                templatestring = templatestring + sr.ReadToEnd();

                sr.Close();
                fs.Close();
            }
            catch (Exception ex)
            {

            }

            return templatestring;
        }

        private string GetBaseConnectionTemplate()
        {
            string templatestring = "";
            try
            {
                FileStream fs = File.OpenRead("templates\\connectionbase.template");
                StreamReader sr = new StreamReader(fs);
                templatestring = sr.ReadToEnd();

                sr.Close();
                fs.Close();
            }
            catch (Exception ex)
            {

            }

            return templatestring;
        }

        private string GetNonQueryTemplate()
        {
            string templatestring = "\n\n";
            try
            {
                FileStream fs = File.OpenRead("templates\\csharptnonquery.template");
                StreamReader sr = new StreamReader(fs);
                templatestring = templatestring + sr.ReadToEnd();

                sr.Close();
                fs.Close();
            }
            catch (Exception ex)
            {

            }

            return templatestring;
        }

        private string GetfileTemplate()
        {
            string templatestring = "";
            try
            {
                FileStream fs = File.OpenRead("templates\\filebase.template");
                StreamReader sr = new StreamReader(fs);
                templatestring = sr.ReadToEnd();

                sr.Close();
                fs.Close();
            }
            catch (Exception ex)
            {

            }

            return templatestring;
        }

        private string Gettempstub()
        {
            string templatestring = "";
            try
            {
                FileStream fs = File.OpenRead(tempfilename);
                StreamReader sr = new StreamReader(fs);
                templatestring = sr.ReadToEnd();

                sr.Close();
                fs.Close();
            }
            catch (Exception ex)
            {

            }

            return templatestring;
        }

        public void GenerateClass(string className,string filenamespace)
        {
            string tempfuncstub = "";
            string classstub = "";
            FileStream fs = null;
            StreamWriter sw = null;
            try
            {
                tempfuncstub = this.Gettempstub();

                if (File.Exists(tempfilename))
                    File.Delete(tempfilename);

                classstub = this.GetfileTemplate();
                classstub = classstub.Replace(CLASS_NAME, className);
                classstub = classstub.Replace(NAMESPACE, filenamespace);
                classstub = classstub.Replace(CLASS_BODY, tempfuncstub);
                fs = File.Open(FileName, FileMode.Append);
                sw = new StreamWriter(fs);
                sw.Write(classstub);
                sw.Close();
                fs.Close();

               this.GenerateBaseConnectionClass(filenamespace);
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (fs != null)
                    fs.Close();
                if (sw != null)
                    sw.Close();
            }
        }

        private void GenerateBaseConnectionClass(string classnamespace)
        {
            string baseconnection = "";
            FileStream fs = null;
            StreamWriter sw = null;
            try
            {
                baseconnection = this.GetBaseConnectionTemplate();
                baseconnection = baseconnection.Replace(NAMESPACE, classnamespace);
                fs = File.Open(connectionbasefilename, FileMode.Append);
                sw = new StreamWriter(fs);
                sw.Write(baseconnection);
                sw.Close();
                fs.Close();
            }
            catch (Exception ex)
            {

            }
            finally
            {
                if (fs != null)
                    fs.Close();
                if (sw != null)
                    sw.Close();
            }
        }

    }
}
